﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;
using System.Xml.Serialization;

namespace RantopadService.Business
{
    public class UserBusiness
    {
        public static DataTable getAllUsers()
        {
            string mysql = "SELECT * from user ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();          
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }

        public static string getUserName(string email)
        {
            string mysql = "SELECT * from user where EMAIL=\"" + email + "\"";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return ""; }
            var username = dr["USER_NAME"].ToString();
            dr.Close();

            myconnection.Close();
            return username;
        }

        public static bool DoLogin(string name, string psw)
        {
            string mysql = "SELECT * from user where USER_NAME=\"" + name + "\"";
            //创建数据库连接

            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return false; }
            var drPwd = dr["USER_PSW"].ToString();
            
            if (dr != null)
            {
                if (drPwd == psw)
                {
                    dr.Close();
                    myconnection.Close();

                    updateUserOnline(name);
                    return true;
                }
                    
            }
            dr.Close();
            myconnection.Close();
            return false;
        }
        public static bool DoLoginByEmail(string email, string psw)
        {
            string mysql = "SELECT * from user where EMAIL=\"" + email + "\"";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return false; }
            var drPwd = dr["USER_PSW"].ToString();
            var userName = dr["USER_NAME"].ToString();
            myconnection.Close();
            if (dr != null)
            {
                if (drPwd == psw)
                {
                    updateUserOnline(userName);
                    dr.Close();
                    return true;
                }

            }
            dr.Close();

            return false;
        }
        public static bool ChangePSW(string name, string psw,string newPsw)
        {
            if(DoLogin(name,psw))
            {
                if(updateUserPsw(name, newPsw)>0)
                    return true;
            }
            return false;
        }
        private static int updateUserOnline(string name)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user` set `ONLINE`='{1}'  where `USER_NAME`='{0}'";
                mysql = string.Format(mysql, name, ""+1);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        private static int updateUserPsw(string name,string newPsw)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user` set `USER_PSW`='{1}'  where `USER_NAME`='{0}'";
                mysql = string.Format(mysql, name, newPsw);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        public static int updateUserPswByEmail(string email, string newPsw)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user` set `USER_PSW`='{1}'  where `email`='{0}'";
                mysql = string.Format(mysql, email, newPsw);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        public static int DoLogout(string name)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user` set `OLINE`='{1}'  where `USER_NAME`='{0}'";
                mysql = string.Format(mysql, name, "" + 0);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
        public static DataTable getAllUsers(string wherelike,string online)
        {
            string mysql = "SELECT * from user  where (USER_NAME like '%{0}%' or EMAIL like '%{0}%') and ONLINE like '%{1}%'";
            mysql = string.Format(mysql, wherelike, online);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static int getAllUsersCount()
        {
            string mysql = "SELECT count(*) as count from user ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();
            myconnection.Close();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            return count;
        }
        public static int getAllUsersCount(string wherelike, string online)
        {
            string mysql = "SELECT count(USER_NAME) as count from user where (USER_NAME like '%{0}%' or EMAIL like '%{0}%')  and ONLINE like '%{1}%'";
            mysql = string.Format(mysql, wherelike,online);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();
            myconnection.Close();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            return count;
        }
        public static int checkUserId(string userID)
        {
            var count = checkData("USER_NAME",userID);
            return count;
        }
        public static int checkData(string colNM,string userID)
        {
            string mysql = "SELECT count(USER_NAME) as count from user where {0} = '{1}' ";
            mysql = string.Format(mysql,colNM, userID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            return count;
        }

        public static int checkUserEmail(string userEmail)
        {
            var count = checkData("EMAIL", userEmail);
            return count;
        }
        public static int delUserByName(string userName)
        {
            string mysql = "delete from user where USER_NAME='{0}'";
            mysql = string.Format(mysql, userName);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            
            var count=mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }



        public static int addUser(string email, string name, string psd)
        {
            //INSERT INTO `rantopad`.`user` (`USER_NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "insert into `user` (`USER_NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('{0}', '{1}', '{2}', '0') ";
                mysql = string.Format(mysql,  name, psd,email);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }
            

            return count;
        }

        internal static int updateUser(string email, string name, string psd)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user` set `EMAIL`='{2}', `USER_PSW`='{1}'  where `USER_NAME`='{0}'";
                mysql = string.Format(mysql, name, psd, email);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }

            return count;
        }
    }
}